﻿using System.Data;
using System.Data.SqlClient;

namespace centraldba_library.Data
{
    public class SqlHelper
    {
        #region " GetCommand Variants "

        public static SqlCommand GetCommand(string connectionString)
        {
            SqlConnection cn = new SqlConnection(connectionString);
            cn.Open();
            SqlCommand cm = cn.CreateCommand();
            return cm;
        }

        public static SqlCommand GetCommand(string connectionString, string command, CommandType ct)
        {
            SqlCommand cm = GetCommand(connectionString);
            cm.CommandText = command;
            cm.CommandType = ct;

            return cm;
        }

        #endregion

        #region " OpenConnection Variants "

        public static string GetConnectionString(string serverName, string databaseName)
        {
            return "Persist Security Info=False;Integrated Security=true;Initial Catalog=" + databaseName + ";server=" + serverName;
        }
        public static SqlConnection OpenConnection(string connectionString, SqlInfoMessageEventHandler errorHandler)
        {
            // Preprocess parameters
            connectionString = connectionString.Trim();
            if (connectionString.Length.Equals(0))
                throw new System.Exception("A connectionString is required to open a connection.");

            // Create the requested SqlConnection
            SqlConnection cn = new SqlConnection(connectionString);

            // Add the error handler, if passed in
            if (errorHandler != null && errorHandler is SqlInfoMessageEventHandler)
            {
                cn.FireInfoMessageEventOnUserErrors = true;
                cn.InfoMessage += errorHandler;
            }

            // Open the Connection
            cn.Open();

            // Return the requested SqlConnection
            return cn;
        }

        public static SqlConnection OpenConnection(string connectionString)
        {
            return OpenConnection(connectionString, (SqlInfoMessageEventHandler)null);
        }

        public static SqlConnection OpenConnection(string serverName, string databaseName, SqlInfoMessageEventHandler errorHandler)
        {
            // Preprocess parameters
            serverName = serverName.Trim();
            databaseName = databaseName.Trim();

            if (serverName.Length.Equals(0) || databaseName.Length.Equals(0))
                throw new System.Exception("A serverName and databaseName are both required to open a connection.");

            // Create the requested SqlConnection
            string cnStr = "Persist Security Info=False;Integrated Security=true;Initial Catalog=" + databaseName + ";server=" + serverName;
            
            // Return the requested SqlConnection
            return OpenConnection(cnStr, errorHandler);
        }

        public static SqlConnection OpenConnection(string serverName, string databaseName)
        {
            return OpenConnection(serverName, databaseName, null);
        }

        #endregion

        #region " CloseConnection Variants "

        public static void CloseConnection(SqlConnection cn)
        {
            CloseConnection(cn, null, null);
        }

        public static void CloseConnection(SqlConnection cn, SqlCommand cm)
        {
            CloseConnection(cn, cm, null);
        }

        public static void CloseConnection(SqlCommand cm)
        {
            CloseConnection(cm, null);
        }

        public static void CloseConnection(SqlCommand cm, SqlDataReader dr)
        {
            if (cm != null)
            {
                if (cm.Connection != null)
                {
                    SqlConnection cn = cm.Connection;

                    CloseConnection(cn, cm, dr);
                }
            }
        }

        public static void CloseConnection(SqlConnection cn, SqlCommand cm, SqlDataReader dr)
        {
            // Release Objects
            if (dr != null)
            {
                if (!dr.IsClosed)
                    dr.Close();

                dr.Dispose();
                dr = null;
            }

            if (cm != null)
            {
                cm.Dispose();
                cm = null;
            }

            if (cn != null)
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();

                cn.Dispose();
                cn = null;
            }
        }

        #endregion

    }
}
